﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using iDMS.Models;
using System.IO;
using System.Text;
using System.Web.Security;

namespace iDMS.Controllers
{
    [CustomAuthorize(Roles = "Normal User")]
    public class DrawingController : Controller
    {
        //
        // GET: /Drawing/
        private DMSRepository db = new DMSRepository();


        private List<SelectListItem> populateDesignChanges()
        {
            return populateEntity(db.DesignChangesGetAll().Where(d => d.PhaseID == 2), "DesignChangeID", "IndentifierAndTitle");
        }

        private List<SelectListItem> populateManufacturers()
        {
            return populateEntity(db.ManufacturersGetAll().Where(m => m.isActive == true && m.isHome == false), "ManufacturerID", "Name");
        }

        private List<SelectListItem> populateUnits()
        {
            return populateEntity(db.UnitsGetAll(), "UnitID", "Name");
        }

        private IQueryable<Drawing> FilterDrawingRoles(IQueryable<Drawing> entities)
        {
            if (!User.IsInRole("Archive"))
            {
                entities = entities.Where(d => d.isWithdrawn == false);
            }
            if (!User.IsInRole("Creation") && !User.IsInRole("Approve") && !User.IsInRole("Release"))
            {
                entities = entities.Where(d => d.DesignChange.PhaseID == 5);
            }
            return entities;
        }

        private IQueryable<Sheet> FilterSheetsRoles(IQueryable<Sheet> entities)
        {
            if (!User.IsInRole("Archive"))
            {
                entities = entities.Where(s => s.LifeCycleStatusID < 5);
            }
            if (!User.IsInRole("Creation") && !User.IsInRole("Approve") && !User.IsInRole("Release"))
            {
                entities = entities.Where(s => s.LifeCycleStatusID == 4);
            }
            return entities;
        }

        //public ActionResult Index()
        //{
        //    var drawings = db.DrawingGetAll();
        //    ViewBag.TotalNumber = drawings.Count();
        //    drawings = drawings.OrderBy(f => f.LastModified).Take(25);

        //    return View(drawings);
        //}

        public ActionResult Index(string search)
        {
            IQueryable<Drawing> list = db.DrawingGetAll();
            list = FilterDrawingRoles(list);
            ViewBag.TotalNumber = list.Count();
            if (!string.IsNullOrEmpty(search))
            {
                list = list.Where(m => m.Name.ToUpper().Contains(search.ToUpper()) || m.Identifier.ToUpper().Contains(search.ToUpper()));
            }
            list = list.OrderBy(m => m.Identifier);
            return View(list.ToList());
        }

        public ActionResult Details(int id)
        {
            Drawing entity = db.DrawingGetAll().SingleOrDefault(d => d.DrawingID == id); ;
            if (entity == null)
            {
                return HttpNotFound();
            }
            ViewBag.Sheets = db.SheetsGetAll().Where(d => d.DrawingID == entity.DrawingID).ToList();
            return View(entity);
        }

        [CustomAuthorize(Roles = "Creation")]
        public ActionResult Create()
        {
            ViewBag.DesignChanges = populateDesignChanges();
            ViewBag.Manufacturers = populateManufacturers();
            ViewBag.Units = populateUnits();
            ViewBag.isHouse = false;
            return View();
        }

        [HttpPost]
        [CustomAuthorize(Roles = "Creation")]
        public ActionResult Create(FormCollection collection)
        {
            Employee employee = db.EmployeesGetAll().SingleOrDefault(e => e.UserGUID == (Guid)Membership.GetUser().ProviderUserKey);
            if (employee == null)
            {
                ViewBag.Message = Messages.errNoEmployee;
                return View("Message");
            }
            ViewBag.DesignChanges = populateDesignChanges();
            ViewBag.Manufacturers = populateManufacturers();
            ViewBag.Units = populateUnits();
            if (collection["isForeign"] == null)
            {
                ViewBag.isHouse = true;
            }
            else
            {
                ViewBag.isHouse = false;
            }
            Drawing newDrawing = new Drawing();
            bool createSuceeded;
            try
            {
                UpdateModel(newDrawing);
                if (collection["isForeign"] == null)
                {
                    Manufacturer man = db.ManufacturersGetAll().SingleOrDefault(m => m.isHome == true);
                    newDrawing.Manufacturer = man;
                }
                newDrawing.isAssembly = false;
                newDrawing.CreatorEmployeeID = employee.EmployeeID;
                newDrawing.LastModified = DateTime.Now;
                newDrawing.isWithdrawn = false;
                db.DrawingAdd(newDrawing);
                db.CommitChanges();
                createSuceeded = true;
            }
            catch
            {
                createSuceeded = false;
            }
            if (createSuceeded)
            {
                return RedirectToAction("Details", new { id = newDrawing.DrawingID });
            }
            else
            {
                ModelState.AddModelError("", Messages.errOcured);
            }
            return View();
        }

        [CustomAuthorize(Roles = "Creation")]
        public ActionResult Edit(int id)
        {
            Drawing model = db.DrawingGetAll().SingleOrDefault(d => d.DrawingID == id);
            if (model == null)
            {
                return HttpNotFound();
            }
            if (model.DesignChange.PhaseID != 2)
            {
                ViewBag.Message = Messages.errNoPending;
                return View("Message");
            }
            ViewBag.isHouse = model.Manufacturer.isHome;
            ViewBag.DesignChanges = populateDesignChanges();
            ViewBag.Manufacturers = populateManufacturers();
            ViewBag.Units = populateUnits();
            return View(model);
        }

        [HttpPost]
        [CustomAuthorize(Roles = "Creation")]
        public ActionResult Edit(int id, FormCollection collection)
        {
            Drawing model = db.DrawingGetAll().SingleOrDefault(d => d.DrawingID == id);
            if (model == null)
            {
                return HttpNotFound();
            }
            if (model.DesignChange.PhaseID != 2)
            {
                ViewBag.Message = Messages.errNoPending;
                return View("Message");
            }
            ViewBag.DesignChanges = populateDesignChanges();
            ViewBag.Manufacturers = populateManufacturers();
            ViewBag.Units = populateUnits();
            ViewBag.isHouse = model.Manufacturer.isHome;

            bool createSuceeded;
            try
            {
                model.ClearManufacturer();
                UpdateModel(model);
                if (collection["isForeign"] == null)
                {
                    Manufacturer man = db.ManufacturersGetAll().SingleOrDefault(m => m.isHome == true);
                    model.Manufacturer = man;
                }
                db.CommitChanges();
                createSuceeded = true;

            }
            catch (Exception e)
            {
                ModelState.AddModelError("", e.Message);
                createSuceeded = false;
            }
            if (createSuceeded)
            {
                return RedirectToAction("Details", new { id = model.DrawingID });
            }
            else
            {
                ModelState.AddModelError("", Messages.errOcured);
            }
            return View(model);
        }

        #region Sheet
        public ActionResult AllSheets()
        {
            var sheets = db.SheetsGetAll();
            return View("Sheets", sheets);
        }

        //id=Drawinig.DrawingID
        public ActionResult Sheets(int id)
        {
            IQueryable<Drawing> drawings = db.DrawingGetAll();
            drawings = FilterDrawingRoles(drawings);
            Drawing drawing = drawings.SingleOrDefault(d => d.DrawingID == id);
            if (drawing == null)
            {
                return HttpNotFound();
            }
            IQueryable<Sheet> list = db.SheetsGetAll();
            list = FilterSheetsRoles(list);
            var sheets = list.Where(d => d.DrawingID == drawing.DrawingID).OrderBy(d => d.PageNmb).ThenBy(d => d.Revision);
            ViewBag.Drawing = drawing;
            ViewBag.FromDC = false;
            return View(sheets);
        }

        //id = designChange
        public ActionResult DesignChangeSheets(int id)
        {
            DesignChange designChange = db.DesignChangesGetAll().SingleOrDefault(d => d.DesignChangeID == id);
            if (designChange == null)
            {
                return new HttpNotFoundResult();
            }
            ViewBag.FromDC = true;
            var model = db.SheetsGetAll().Where(d => d.DesignChangeID == designChange.DesignChangeID || d.InnaplicableChangeID == designChange.DesignChangeID).OrderBy(d => d.Drawing.Identifier);
            return PartialView("_Sheets", model);
        }

        public ActionResult DesignChangeDrawings(int id)
        {
            DesignChange designChange = db.DesignChangesGetAll().SingleOrDefault(d => d.DesignChangeID == id);
            if (designChange == null)
            {
                return new HttpNotFoundResult();
            }
            var model = db.DrawingGetAll().Where(d => d.DesignChangeID == designChange.DesignChangeID || d.InnaplicableDesignChangeID == designChange.DesignChangeID).OrderBy(d => d.Identifier);
            return PartialView("_Drawings", model);
        }

        public ActionResult ManufacturersDrawings(int id)
        {
            Manufacturer manufacturer = db.ManufacturersGetAll().SingleOrDefault(m => m.ManufacturerID == id);
            if (manufacturer == null)
            {
                return new HttpNotFoundResult();
            }
            var model = db.DrawingGetAll().Where(d => d.ManufacturerID == manufacturer.ManufacturerID);
            return PartialView("_Drawings", model);
        }

        private int determineNewPageNmb(Drawing drawing)
        {
            int pageNmb = 0;
            if (drawing.Sheets.Count == 0)
            {
                pageNmb = 1;
            }
            else
            {
                pageNmb = drawing.Sheets.OrderBy(p => p.PageNmb).Last().PageNmb + 1;
            }

            return pageNmb;
        }

        private List<SelectListItem> populateSheetFormats()
        {
            List<SelectListItem> result = new List<SelectListItem>();
            SelectList sl = new SelectList(db.SheetFormatGetAll().ToList(), "FormatID", "FullName");
            return sl.ToList();
        }

        private List<SelectListItem> populateEntity<T>(IQueryable<T> entity, string dataValueField, string dataTextField)
        {
            List<SelectListItem> result = new List<SelectListItem>();
            SelectList sl = new SelectList(entity.ToList(), dataValueField, dataTextField);
            return sl.ToList();
        }

        //id  = DrawingID
        [CustomAuthorize(Roles = "Creation")]
        public ActionResult SheetCreate(int id)
        {
            Drawing drawing = db.DrawingGetAll().SingleOrDefault(d => d.DrawingID == id); //db.FindDrawing(id);
            if (drawing == null)
            {
                return HttpNotFound();
            }
            ViewBag.Drawing = drawing;
            Sheet sheet = new Sheet();
            sheet.DrawingID = drawing.DrawingID;

            sheet.PageNmb = determineNewPageNmb(drawing);
            ViewBag.Formats = populateEntity(db.SheetFormatGetAll(), "FormatID", "FullName");
            ViewBag.Carriers = populateEntity(db.InformationCarriersGetAll(), "CarrierID", "Name");
            ViewBag.DChanges = populateEntity(db.DesignChangesGetAll().Where(d => d.PhaseID == 2), "DesignChangeID", "IndentifierAndTitle");
            ViewBag.isReplace = false;

            return View(sheet);
        }

        [HttpPost]
        [CustomAuthorize(Roles = "Creation")]
        public ActionResult SheetCreate(int id, FormCollection collection)
        {
            Drawing drawing = db.DrawingGetAll().SingleOrDefault(d => d.DrawingID == id);
            if (drawing == null)
            {
                return HttpNotFound();
            }
            Employee employee = db.EmployeesGetAll().SingleOrDefault(e => e.UserGUID == (Guid)Membership.GetUser().ProviderUserKey);
            if (employee == null)
            {
                ViewBag.Message = Messages.errNoEmployee;
                return View("Message");
            }
            ViewBag.Drawing = drawing;
            ViewBag.Formats = populateEntity(db.SheetFormatGetAll(), "FormatID", "FullName");
            ViewBag.Carriers = populateEntity(db.InformationCarriersGetAll(), "CarrierID", "Name");
            ViewBag.DChanges = populateEntity(db.DesignChangesGetAll().Where(d => d.PhaseID == 2), "DesignChangeID", "IndentifierAndTitle");
            ViewBag.isReplace = false;
            Sheet sheet = new Sheet();
            bool createSuceeded;
            if (ModelState.IsValid)
            {
                try
                {
                    UpdateModel(sheet);
                    sheet.DrawingID = drawing.DrawingID;
                    if (drawing.DesignChange.isActive)
                    {
                        sheet.DesignChangeID = drawing.DesignChangeID;
                    }

                    sheet.DrawingID = drawing.DrawingID;
                    sheet.Revision = 0;
                    sheet.EmployeeID = employee.EmployeeID;
                    sheet.LifeCycleStatusID = 1;
                    sheet.UnderRevisionWithdrawal = null;
                    sheet.CreateDate = DateTime.Now;
                    db.SheetAdd(sheet);
                    db.CommitChanges();
                    createSuceeded = true;
                    //
                }
                catch
                {
                    createSuceeded = false;
                }
                if (createSuceeded)
                {
                    return RedirectToAction("Sheets", new { id = sheet.DrawingID });
                }
                else
                {
                    ModelState.AddModelError("", Messages.errOcured);
                }
            }
            return View(sheet);
        }

        //id = sheetID
        [CustomAuthorize(Roles = "Creation")]
        public ActionResult SheetReplace(int id)
        {
            Sheet sheetOld = db.SheetsGetAll().SingleOrDefault(s => s.ScheetID == id);
            if (sheetOld == null)
            {
                return HttpNotFound();
            }
            ViewBag.SheetOld = sheetOld;
            Sheet sheet = new Sheet();
            sheet.DrawingID = sheetOld.DrawingID;
            sheet.Revision = sheetOld.Revision++;
            sheet.PageNmb = sheetOld.PageNmb;
            ViewBag.Drawing = db.DrawingGetAll().SingleOrDefault(d => d.DrawingID == sheetOld.DrawingID);
            ViewBag.Formats = populateEntity(db.SheetFormatGetAll(), "FormatID", "FullName");
            ViewBag.Carriers = populateEntity(db.InformationCarriersGetAll(), "CarrierID", "Name");
            ViewBag.DChanges = populateEntity(db.DesignChangesGetAll().Where(d => d.PhaseID == 2 && d.DesignChangeID != sheetOld.DesignChangeID), "DesignChangeID", "IndentifierAndTitle");
            ViewBag.isReplace = true;
            return View(sheet);
        }

        [HttpPost]
        [CustomAuthorize(Roles = "Creation")]
        public ActionResult SheetReplace(int id, FormCollection collection)
        {
            Employee employee = db.EmployeesGetAll().SingleOrDefault(e => e.UserGUID == (Guid)Membership.GetUser().ProviderUserKey);
            if (employee == null)
            {
                ViewBag.Message = Messages.errNoEmployee;
                return View("Message");
            }
            Sheet sheetOld = db.SheetsGetAll().SingleOrDefault(s => s.ScheetID == id);
            if (sheetOld == null)
            {
                return HttpNotFound();
            }

            ViewBag.Drawing = db.DrawingGetAll().SingleOrDefault(d => d.DrawingID == sheetOld.DrawingID);
            ViewBag.Formats = populateEntity(db.SheetFormatGetAll(), "FormatID", "FullName");
            ViewBag.Carriers = populateEntity(db.InformationCarriersGetAll(), "CarrierID", "Name");
            ViewBag.DChanges = populateEntity(db.DesignChangesGetAll().Where(d => d.PhaseID == 2 && d.DesignChangeID != sheetOld.DesignChangeID), "DesignChangeID", "IndentifierAndTitle");
            ViewBag.isReplace = true;
            bool replaceSucceded;
            Sheet sheet = new Sheet();
            if (ModelState.IsValid)
            {
                try
                {
                    UpdateModel(sheet);
                    sheet.DrawingID = sheetOld.DrawingID;
                    sheet.Revision = sheetOld.Revision;
                    sheet.Revision++;
                    sheet.EmployeeID = employee.EmployeeID;
                    sheet.LifeCycleStatusID = 1;
                    sheet.PageNmb = sheetOld.PageNmb;
                    sheet.CreateDate = DateTime.Now;
                    sheetOld.InnaplicableChangeID = sheet.DesignChangeID;
                    sheetOld.UnderRevisionWithdrawal = true;
                    db.SheetAdd(sheet);
                    db.CommitChanges();
                    replaceSucceded = true;
                }
                catch
                {
                    replaceSucceded = false;
                }
                if (replaceSucceded)
                {
                    return RedirectToAction("Sheets", new { id = sheet.DrawingID });
                }
                else
                {
                    ModelState.AddModelError("", Messages.errOcured);
                }
            }
            return View(sheet);
        }

        #region Withdrawn

        [CustomAuthorize(Roles = "Creation")]
        public ActionResult Withdrawn(int id)
        {
            Drawing model = db.DrawingGetAll().SingleOrDefault(d => d.DrawingID == id);
            if (model == null)
            {
                return new HttpNotFoundResult();
            }
            ViewBag.DChanges = populateEntity(db.DesignChangesGetAll().Where(d => d.PhaseID == 2 && d.DesignChangeID != model.DesignChangeID), "DesignChangeID", "IndentifierAndTitle");
            return View(model);
        }

        [HttpPost]
        [CustomAuthorize(Roles = "Creation")]
        public ActionResult Withdrawn(int id, FormCollection collection)
        {
            Drawing model = db.DrawingGetAll().SingleOrDefault(d => d.DrawingID == id);
            if (model == null)
            {
                return new HttpNotFoundResult();
            }
            ViewBag.DChanges = populateEntity(db.DesignChangesGetAll().Where(d => d.PhaseID == 2 && d.DesignChangeID != model.DesignChangeID), "DesignChangeID", "IndentifierAndTitle");
            bool succeeded;
            try
            {
                UpdateModel(model);
                db.CommitChanges();
                succeeded = true;
            }
            catch
            {
                succeeded = false;
            }
            if (succeeded)
            {
                return RedirectToAction("Details", new { id = model.DrawingID });
            }
            else
            {
                ModelState.AddModelError("", Messages.errOcured);
            }
            return View(model);
        }

        [CustomAuthorize(Roles = "Creation")]
        public ActionResult SheetWithdrawn(int id)
        {
            Sheet model = db.SheetsGetAll().SingleOrDefault(s => s.ScheetID == id);
            if (model == null)
            {
                return new HttpNotFoundResult();
            }
            ViewBag.DChanges = populateEntity(db.DesignChangesGetAll().Where(d => d.PhaseID == 2 && d.DesignChangeID != model.DesignChangeID), "DesignChangeID", "IndentifierAndTitle");
            return View(model);
        }



        [HttpPost]
        [CustomAuthorize(Roles = "Creation")]
        public ActionResult SheetWithdrawn(int id, FormCollection collection)
        {

            Sheet sheet = db.SheetsGetAll().SingleOrDefault(s => s.ScheetID == id);
            if (sheet == null)
            {
                return new HttpNotFoundResult();
            }
            ViewBag.DChanges = populateEntity(db.DesignChangesGetAll().Where(d => d.PhaseID == 2 && d.DesignChangeID != sheet.DesignChangeID), "DesignChangeID", "IndentifierAndTitle");
            bool suceeded;
            try
            {
                UpdateModel(sheet);
                //TODO: Do some test.
                sheet.UnderRevisionWithdrawal = false;

                db.CommitChanges();
                suceeded = true;
            }
            catch
            {
                suceeded = false;
            }
            if (suceeded)
            {
                return RedirectToAction("Sheets", new { id = sheet.DrawingID });
            }
            else
            {
                ModelState.AddModelError("", Messages.errOcured);
            }
            return View(sheet);
        }
        #endregion

        [CustomAuthorize(Roles = "Creation")]
        public ActionResult SheetDelete(int id)
        {
            Sheet sheet = db.SheetsGetAll().SingleOrDefault(s => s.ScheetID == id);
            if (sheet == null)
            {
                return new HttpNotFoundResult();
            }
            return View(sheet);
        }

        [HttpPost]
        [CustomAuthorize(Roles = "Creation")]
        public ActionResult SheetDelete(int id, FormCollection collection)
        {
            Sheet model = db.SheetsGetAll().SingleOrDefault(d => d.ScheetID == id);
            if (model == null)
            {
                return new HttpNotFoundResult();
            }
            bool suceeded;
            try
            {
                db.SheetDelete(model);

                db.CommitChanges();
                suceeded = true;
            }
            catch (Exception e)
            {
                ModelState.AddModelError("", e.Message);
                suceeded = false;
            }
            if (suceeded)
            {
                return RedirectToAction("Sheets", new { id = model.DrawingID });
            }
            else
            {
                ModelState.AddModelError("", Messages.errOcured);
            }
            return View(model);
        }

        #region Upload&Download
        //id = SheetID
        public ActionResult SheetDownload(int id)
        {
            Sheet sheet = db.SheetsGetAll().SingleOrDefault(s => s.ScheetID == id);
            if (sheet == null)
            {
                return new HttpNotFoundResult();
            }
            //declare byte array to get file content from database and string to store file name
            byte[] fileData;
            string fileName;
            //create object of LINQ to SQL class
            //using LINQ expression to get record from database for given id value

            var Resource = db.ResourcesGetAll().SingleOrDefault(r => r.ResourceID == sheet.ResourceID);
            if (Resource == null)
            {
                return new HttpNotFoundResult();
            }
            //only one record will be returned from database as expression uses condtion on primary field
            //so get first record from returned values and retrive file content (binary) and filename 
            fileData = (byte[])Resource.FileData.ToArray();
            fileName = sheet.Drawing.Identifier + "_" + sheet.PageNmb;
            if (sheet.Revision > 0)
            {
                fileName = fileName + "_" + sheet.RevisionSymbol;
            }
            fileName = fileName + "." + Resource.FileExtension;
            //return file and provide byte file content and file name
            return File(fileData, Resource.ContentType, fileName);
        }

        //id=SheetID

        [CustomAuthorize(Roles = "Creation")]
        public ActionResult SheetUpload(int id)
        {
            Sheet sheet = db.SheetsGetAll().SingleOrDefault(s => s.ScheetID == id);
            if (sheet == null)
            {
                return new HttpNotFoundResult();
            }

            if (sheet.LifeCycleStatusID != 1)
            {
                return new HttpNotFoundResult();
            }
            return View(sheet);
        }

        public string UploadAttachment(int id, HttpPostedFileBase fileData)
        {
            //var fileName = this.Server.MapPath("~/uploads/" + System.IO.Path.GetFileName(fileData.FileName));
            //fileData.SaveAs(fileName);
            Sheet sheet = db.SheetsGetAll().SingleOrDefault(s => s.ScheetID == id);
            if (sheet == null)
            {
                return "Sheet not found";
            }

            if (sheet.LifeCycleStatusID != 1)
            {
                return "Sheet is not in preparation status";
            }

            var result = FileUploadHelper.SaveFile(1000000, "", fileData, HttpContext.Server);

            if (!result.Success)
            {
                var builder = new StringBuilder();
                result.Errors.ForEach(e => builder.AppendLine(e));
                return builder.ToString();
            }

            bool createSuceed;
            try
            {
                SheetAttachment attachment = new SheetAttachment();
                attachment.Name = System.IO.Path.GetFileNameWithoutExtension(fileData.FileName);
                attachment.ResourceID = result.ResouceID;
                attachment.LastModified = DateTime.Now;
                attachment.SheetID = sheet.ScheetID;
                attachment.isPublic = true;
                db.SheetAttachmentAdd(attachment);
                db.CommitChanges();
                createSuceed = true;
            }
            catch
            {
                createSuceed = false;
                db.ResourceDelete(db.ResourcesGetAll().SingleOrDefault(r => r.ResourceID == result.ResouceID));
            }
            if (!createSuceed)
            {
                return Messages.errorDBUpdate;
            }

            return Messages.infFilesSucced;


        }

        public string Upload(int id, HttpPostedFileBase fileData)
        {

            //var fileName = this.Server.MapPath("~/uploads/" + System.IO.Path.GetFileName(fileData.FileName));
            //fileData.SaveAs(fileName);
            Sheet sheet = db.SheetsGetAll().SingleOrDefault(s => s.ScheetID == id);
            if (sheet == null)
            {
                return "Sheet not found";
            }

            if (sheet.LifeCycleStatusID != 1)
            {
                return "Sheet is not in preparation status";
            }

            var result = FileUploadHelper.SaveFile(1000000, "pdf,tiff,tif", fileData, HttpContext.Server);

            if (!result.Success)
            {
                var builder = new StringBuilder();
                result.Errors.ForEach(e => builder.AppendLine(e));
                return builder.ToString();
            }

            bool sheetUdpateSuceed;
            try
            {
                int? oldResID = sheet.ResourceID;
                sheet.ResourceID = result.ResouceID;
                if (oldResID.HasValue)
                {
                    Resource oldRes = db.ResourcesGetAll().SingleOrDefault(r => r.ResourceID == oldResID);
                    db.ResourceDelete(oldRes);
                }

                db.CommitChanges();
                sheetUdpateSuceed = true;
            }
            catch
            {
                sheetUdpateSuceed = false;
                db.ResourceDelete(db.ResourcesGetAll().SingleOrDefault(r => r.ResourceID == result.ResouceID));
            }
            if (!sheetUdpateSuceed)
            {
                return "Update Database error";
            }

            return "The file was succesfully uploaded";
        }
        #endregion

        //id = SheetID
        public ActionResult AttachmentsWindow(int id)
        {
            Sheet sheet = db.SheetsGetAll().SingleOrDefault(s => s.ScheetID == id);
            if (sheet == null)
            {
                return new HttpNotFoundResult();
            }
            var list = db.SheetAttachmentGetAll().Where(s => s.SheetID == sheet.ScheetID);
            return PartialView("_AttachmentsWindow", list.ToList());
        }

        public ActionResult SheetResource(int id)
        {
            Sheet model = db.SheetsGetAll().SingleOrDefault(s => s.ScheetID == id);
            if (model == null)
            {
                return Content(Messages.errSheetNotFound);
            }
            return PartialView("_SheetResource", model);
        }

        public ActionResult AttachmentDownload(int id)
        {
            SheetAttachment attachment = db.SheetAttachmentGetAll().SingleOrDefault(s => s.AttachmentID == id);
            if (attachment == null)
            {
                return new HttpNotFoundResult();
            }
            //declare byte array to get file content from database and string to store file name
            byte[] fileData;
            string fileName;
            //create object of LINQ to SQL class
            //using LINQ expression to get record from database for given id value

            var Resource = db.ResourcesGetAll().SingleOrDefault(r => r.ResourceID == attachment.ResourceID);
            if (Resource == null)
            {
                return new HttpNotFoundResult();
            }
            //only one record will be returned from database as expression uses condtion on primary field
            //so get first record from returned values and retrive file content (binary) and filename 
            fileData = (byte[])Resource.FileData.ToArray();
            fileName = attachment.Name;
            fileName = fileName + "." + Resource.FileExtension;
            //return file and provide byte file content and file name
            return File(fileData, Resource.ContentType, fileName);
        }


        #endregion

        public ActionResult DeleteUnusedRsx()
        {
            List<Resource> rsxs = db.ResourcesGetAll().ToList();
            List<Resource> toDel = new List<Resource>();
            ViewBag.Total = rsxs.Count;

            foreach (Resource item in rsxs)
            {
                if (!db.SheetsGetAll().Where(d => d.ResourceID == item.ResourceID).Any() && !db.SheetAttachmentGetAll().Where(d => d.ResourceID == item.ResourceID).Any())
                {
                    toDel.Add(item);
                }
            }
            ViewBag.Unused = toDel.Count;

            return View(toDel);
        }

        public ActionResult DeleteUnusedRsx2()
        {
            List<Resource> rsxs = db.ResourcesGetAll().ToList();
            List<Resource> toDel = new List<Resource>();


            foreach (Resource item in rsxs)
            {
                if (!db.SheetsGetAll().Where(d => d.ResourceID == item.ResourceID).Any() && !db.SheetAttachmentGetAll().Where(d => d.ResourceID == item.ResourceID).Any())
                {
                    toDel.Add(item);
                }
            }

            foreach (Resource item in toDel)
            {
                db.ResourceDelete(item);
            }
            db.CommitChanges();
            return RedirectToAction("Index");
        }
    }


}
